﻿using Dapper;
using Sql_Windows_Sp_Tools.Entity;
using System.Data;
using System.Data.SqlClient;

namespace Sql_Windows_Sp_Tools.Repository
{
    public class DataBaseVersionRep
    {
        private string _DBConn;
        public DataBaseVersionRep(string DBConn)
        {
            _DBConn = DBConn;
        }

        public bool IsCreate() {
            var sql = @"
                         select TOP 1  1   
                         from information_schema.tables  
                         where table_schema='dbo' 
                         and table_name='databaseVersion'
                      ";
            using (IDbConnection db = new SqlConnection(_DBConn))
            {
                return db.ExecuteScalar<int>(sql) > 0;
            }
        }

        public bool CreateTable()
        {
            var sql = @$"
                        CREATE TABLE [dbo].[databaseVersion](
	                        [CurrentVersion] [INT] NOT NULL,
	                        [CreateTime] [DATETIME] NOT NULL,
                         CONSTRAINT [PK_databaseVersion] PRIMARY KEY CLUSTERED 
                        (
	                        [Sp_Version] ASC
                        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                        ) ON [PRIMARY]

                        ALTER TABLE [dbo].[databaseVersion] ADD  CONSTRAINT [DF_databaseVersion_CreateTime]  DEFAULT (GETDATE()) FOR [CreateTime]

                        ";
            using (IDbConnection db = new SqlConnection(_DBConn))
            {
                return db.ExecuteScalar<int>(sql) > 0;
            }
        }

        public int Add(int currentVersion)
        {
            var sql = @$"
                        DELETE FROM databaseVersion;
                        INSERT INTO databaseVersion
                        (
                            CurrentVersion,
                            CreateTime
                        )
                        VALUES
                        (
                            {currentVersion},
                            GETDATE()
                        );
                        ";
            using (IDbConnection db = new SqlConnection(_DBConn))
            {
                return db.ExecuteScalar<int>(sql);
            }
        }


        public DataBaseVersionEntity Detail(int currentVersion)
        {
            var sql = @$"SELECT TOP 1
                               *
                        FROM databaseVersion
                        WHERE CurrentVersion >= {currentVersion}
                        ORDER BY CurrentVersion DESC;";
            using (IDbConnection db = new SqlConnection(_DBConn))
            {
                return db.QueryFirstOrDefault<DataBaseVersionEntity>(sql);
            }
        }


        public DataBaseVersionEntity LastDetail() {
            var sql = @"
                SELECT TOP 1 * FROM databaseVersion ORDER BY CurrentVersion DESC;
            ";
            using (IDbConnection db = new SqlConnection(_DBConn))
            {
                return db.QueryFirstOrDefault<DataBaseVersionEntity>(sql);
            }
        }
    }
}
